/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var TgKeywordModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`key_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '关键词ID',
  //`type` int(2) NOT NULL DEFAULT '0' COMMENT '类别: 0未知,1行业,2场景,3设计风格,10自定义',
  //`type_name` varchar(20) NOT NULL DEFAULT '' COMMENT '类别名称',
  //`key_name` varchar(20) NOT NULL DEFAULT '' COMMENT '关键词',
  //`order` int(3) NOT NULL DEFAULT '999' COMMENT '显示顺序',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL DEFAULT '',
  //`del_flg` int(1) NOT NULL DEFAULT '0',


};
TgKeywordModel.prototype = new ItbBaseModel();

// 取得表名
TgKeywordModel.prototype.getTableName = function(){
    return 'tg_keyword';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
TgKeywordModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'key_id='+this.key_id;
}

//------------------------------------------------------------------
/* 根据关键词类别获取关键词
 * 参数:callback:回调
 * 参数 types: 类别［逗号链接］,默认取默认项目[不包括自定义项目]
 * */
TgKeywordModel.prototype.getKeyByType = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' select t.key_id, t.key_name, t.type, t.type_name, t.order '+
            ' from tg_keyword t '+
            ' where t.del_flg=0 ';

        if(!!THIS.types){
            sql += ' and t.type IN ('+ THIS.types + ') ';
        }else{
            sql += ' and t.type != 10 ';
        }
        sql += ' order by t.type, t.order ';

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result);
            }else{
                callback(error, null);
            }
        });
    });
}
//--------------------------------end-------------------------------

//------------------------------------------------------------------
/* 检索目标关键词
 * 参数:callback:回调
 * 参数 find_name: 目标关键词
 * 参数 fuzzy: 1:模糊检索, 其它[默认]:精确检索
 * */
TgKeywordModel.prototype.findKeyByName = function(callback,fuzzy){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' select t.key_id, t.key_name, t.type, t.type_name, t.order '+
            ' from tg_keyword t '+
            ' where t.del_flg=0 ';

        if(fuzzy == 1){
            sql += ' and t.key_name LIKE "%'+ THIS.find_name + '%"';
        } else {
            sql += ' and t.key_name = '+ THIS.find_name;
        }
        sql += ' order by t.type, t.order ';

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result);

            }else{
                callback(error, null);
            }
        });
    });
}

//------------------------------------------------------------------
/* 检索目标关键词
 * 参数:callback:回调
 * 参数 find_name: 目标关键词
 * 参数 fuzzy: 1:模糊检索, 其它[默认]:精确检索
 * */
TgKeywordModel.prototype.findKeyByNames = function(callback,fuzzy){
    var THIS = this;
    var tableName = this.getTableName();

    if(!THIS.find_name || THIS.find_name.length==0){
        return callback(null,null);
    }

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' select t.key_id, t.key_name, t.type, t.type_name, t.order '+
            ' from tg_keyword t '+
            ' where t.del_flg=0 '+
            ' and ( 1=2 ' ;

        // 拼接查询语句
        var arr = THIS.find_name.split(',');
        for(var i=0;i<arr.length;i++){
            if(fuzzy == 1){
                sql += ' or t.key_name LIKE "%'+ arr[i] + '%"';
            } else {
                sql += ' or t.key_name = '+ arr[i] ;
            }
        }

        sql +=' ) ';
        sql += ' order by t.type, t.order ';

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result);

            }else{
                callback(error, null);
            }
        });
    });
}

//------------------------------------------------------------------
/* 检索目标模版
 * 参数 bForCnt: true [仅用于取count]; false [取数据]
 * 参数 keyword: 关键字, 多个单词以半角都好分隔
 * 参数 typeLs:  搜索分类, 0 未分类, 1 海报, 2 多页, 3 ppt, 4 印品 [多个时,逗号连接: "1,2,3"]
 * 参数：callback: 回调, 参数如下:
 *          error: 错误消息
 *          data: 结果数组, 数组元素如下:
 *              "thumb":"http://xxx.jpg", // 如typeL为4则id为印品商品图片,否则为模版图片
 *              "id":788, // 如typeL为4则id为印品商品id,否则为模版id
 *              "typeL":1,
 *              "type_desc":"幻灯片",
 *              "price": "免费",
 *              "author":"by乌头马角工作室",
 *              "time":"2016-12-05 13:38"// 暂定[更新时间]
 * */
// todo 新增接口, 已完成, 待审核 begin
function sql_search(bForCnt, keyword, typeLs, pageNum, pageSize){

    var type_sql = typeLs?(" and r.type_id IN ("+typeLs+") "):(" and 1=1 ");

    var keys = keyword.split(',');
    var locate_sql = " and (";      // sample: " and (locate('奇怪', k.`key_name`)>0  or locate('严肃', k.`key_name`)>0 ) ";
    for(var i=0;i<keys.length;i++){
        if(i>0){
            locate_sql +=" or ";
        }
        locate_sql += " locate('"+keys[i]+"', k.`key_name`)>0 ";
    }
    locate_sql +=") ";

    var sql = "";
    if(bForCnt) {
        sql = " SELECT COUNT(*) AS count ";
    } else {
        sql = " SELECT mid3.typeL, mid3.id "+
            "        	           ,( case mid3.typeL "+
            "                             when 1 then '海报' "+
            "                             when 2 then '多页' "+
            "                             when 3 then '幻灯片' "+
            "                             when 4 then (select t.type_name from tg_design_doc p, tg_type t "+
            "                                           where p.item_id=mid3.id and p.del_flg=0 and p.item_id>0 and t.type_id=p.type_id and t.del_flg=0) "+
            "                             end ) AS type_desc "+
            "        	           ,( case mid3.typeL "+
            "                             when 1 then (select design_name from tg_design_doc p where p.`design_id`=mid3.id and p.del_flg=0) "+
            "                             when 2 then (select design_name from tg_design_doc p where p.`design_id`=mid3.id and p.del_flg=0) "+
            "                             when 3 then (select tmpl_name from itb_template p where p.`tmpl_id`=mid3.id and p.del_flg=0) "+
            "                             when 4 then (select item_name from itb_items p where p.item_id=mid3.id and p.state=20 and p.del_flg=0) "+
            "                             end ) AS name "+
            "        	           ,( case mid3.typeL "+
            "                             when 1 then (select thumb from tg_design_page p where p.`design_id`=mid3.id and p.page_index=0 and p.del_flg=0) "+
            "                             when 2 then (select thumb from tg_design_page p where p.`design_id`=mid3.id and p.page_index=0 and p.del_flg=0) "+
            "                             when 3 then (select thumb_s from itb_tmpl_page p where p.`tmpl_id`=mid3.id and p.page_index=0 and p.del_flg=0) "+
            "                             when 4 then (select func_parse_string_array(thumbs,0)  from itb_items p where p.item_id=mid3.id and p.state=20 and p.del_flg=0) "+
            "                             end ) AS thumb "+
            "        	           ,( case mid3.typeL "+
            "                             when 1 then (select u.nickname from tg_design_doc d, itb_user u where d.`design_id`=mid3.id and d.del_flg=0 and u.user_id=d.user_id) "+
            "                             when 2 then (select u.nickname from tg_design_doc d, itb_user u where d.`design_id`=mid3.id and d.del_flg=0 and u.user_id=d.user_id) "+
            "                             when 3 then (select u.nickname from itb_template d, itb_user u where d.`tmpl_id`=mid3.id and d.del_flg=0 and u.user_id=d.user_id) "+
            "                             when 4 then (select u.nickname from tg_design_doc d, itb_user u where d.item_id=mid3.id and d.del_flg=0 and d.item_id>0 and u.user_id=d.user_id) "+
            "                             end ) AS author "+
            "        	           ,( case mid3.typeL "+
            "                             when 1 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from tg_design_doc d where d.`design_id`=mid3.id and d.del_flg=0) "+
            "                             when 2 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from tg_design_doc d where d.`design_id`=mid3.id and d.del_flg=0) "+
            "                             when 3 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from itb_template d where d.`tmpl_id`=mid3.id and d.del_flg=0) "+
            "                             when 4 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from tg_design_doc d where d.item_id=mid3.id and d.del_flg=0 and d.item_id>0) "+
            "                             end ) AS time "+
            "        	           ,IFNULL( case mid3.typeL "+
            "                             when 1 then (select concat(TRUNCATE(p.price/100, 2),'元') from itb_tmpl_price p where p.tmpl_id=mid3.id and p.type=1 and p.del_flg=0 order by p.price limit 1) "+
            "                             when 2 then (select concat(TRUNCATE(p.price/100, 2),'元') from itb_tmpl_price p where p.tmpl_id=mid3.id and p.type=2 and p.del_flg=0 order by p.price limit 1) "+
            "                             when 3 then (select concat(TRUNCATE(p.price/100, 2),'元') from itb_tmpl_price p where p.tmpl_id=mid3.id and p.type=3 and p.del_flg=0 order by p.price limit 1) "+
            "                             when 4 then (select concat('印刷 ',TRUNCATE(qp.price/100, 2),'元/',qp.show,qp.unit) "+
            "                                            from itb_items t, itb_print_rel pr, itb_print_param pp, itb_qty_price qp "+
            "                                           where t.item_id=mid3.id and t.del_flg=0 and t.state=20 "+
            "                                             and pr.del_flg=0 AND pr.item_id=t.item_id "+
            "                                             and pp.del_flg=0 AND pp.pp_id=pr.pp_id "+
            "                                             and qp.del_flg=0 AND qp.pp_id=pp.pp_id "+
            "                                           order by qp.min limit 1) "+
            "                             end, '免费' ) AS price ";// 价格信息
    }
    sql +=
        " FROM ( select distinct mid2.* "+
        "          from ( select mid.type_id AS typeL, "+
        "        	           ( case mid.type_id "+
        "                             when 1 then mid.doc_id "+
        "                             when 2 then mid.doc_id "+
        "                             when 3 then mid.doc_id "+
        "                             when 4 then (select item_id from itb_items p where p.`design_id`=mid.`doc_id` and p.state=20 and p.del_flg=0) "+
        "                             end ) AS id "+// 印品时取商品id
        "        	           ,( case mid.type_id "+
        "                             when 1 then (select publish from tg_design_doc p where p.`design_id`=mid.`doc_id` and p.del_flg=0) "+
        "                             when 2 then (select publish from tg_design_doc p where p.`design_id`=mid.`doc_id` and p.del_flg=0) "+
        "                             when 3 then (select publish from itb_template p where p.`tmpl_id`=mid.`doc_id` and p.del_flg=0) "+
        "                             when 4 then (select publish from tg_design_doc p, tg_type t WHERE p.`design_id`=mid.`doc_id` AND p.del_flg=0 AND p.item_id>0 AND t.type_id = p.type_id AND t.parent in (40100, 40200, 40300)) "+  // 限制印品搜索, 仅能搜索名片传单展架
        "                             end ) AS publish "+
        "                      from ( select r.doc_id, r.type_id "+
        "                               from tg_keyword_r r "+
        "                              where r.key_id in ( SELECT k.key_id FROM tg_keyword k WHERE k.del_flg=0 "+locate_sql+" ) "+
        "                                and r.del_flg=0 "+
        "                                  "+type_sql+
        "                           ) mid "+
        "               ) mid2 "+
        "         where mid2.publish=1 "+
        "           and mid2.id is not null ";
    if(!bForCnt) {// 取数据时
        var skipCnt = Number(pageNum||'0')*Number(pageSize||'0');
        sql += " limit "+skipCnt+", "+Number(pageSize||'0');
    }
    sql += " ) mid3 ";

    return sql;
}
// todo 新增接口, 已完成, 待审核 end
//------------------------------------------------------------------
/* 检索目标模版
 * 参数 keyword: 关键字, 多个单词以半角都好分隔
 * 参数 typeLs:  搜索分类, 0 未分类, 1 海报, 2 多页, 3 ppt, 4 印品 [多个时,逗号连接: "1,2,3"]
 * 参数 pageNum: 搜索结果对应页
 * 参数 pageSize: 搜索结果请求页大小
 * 参数：callback: 回调, 参数如下:
 *          error: 错误消息
 *          data: 结果数组, 数组元素如下:
 *              "thumb":"http://xxx.jpg", // 如typeL为4则id为印品商品图片,否则为模版图片
 *              "id":788, // 如typeL为4则id为印品商品id,否则为模版id
 *              "typeL":1,
 *              "type_desc":"幻灯片",[modified 2017-03-10]
 *              "price": "免费",[modified 2017-03-10]
 *              "author":"by乌头马角工作室",
 *              "time":"2016-12-05 13:38"// 暂定[更新时间]
 * */
TgKeywordModel.prototype.search = function(keyword, typeLs, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        //var type_sql = typeL?(" and r.type_id="+typeL+" "):(" and 1=1 ");
        /*var type_sql = typeLs?(" and r.type_id IN ("+typeLs+") "):(" and 1=1 ");// Modified 2017-02-18

        var keys = keyword.split(',');
        var locate_sql = " and (";      // sample: " and (locate('奇怪', k.`key_name`)>0  or locate('严肃', k.`key_name`)>0 ) ";
        for(var i=0;i<keys.length;i++){
            if(i>0){
                locate_sql +=" or ";
            }
            locate_sql += " locate('"+keys[i]+"', k.`key_name`)>0 ";
        }
        locate_sql +=") ";

        var sql =
            "			  select mid3.*, u.nickname as author,'' as `desc` "+
            "             from(   select distinct mid2.* "+
            "                     from (  select  mid.type_id as typeL, "+
            "                                	  (   case mid.type_id "+
            "                                                	when 1 then mid.doc_id "+
            "                                                	when 2 then mid.doc_id "+
            "                                                	when 3 then mid.doc_id "+
            "                                                	when 4 then (select item_id from itb_items p where p.`design_id`=mid.`doc_id` and p.del_flg=0) "+
            "                                                	end  "+
            "                                               )as id , "+
            "                                     (   case mid.type_id "+
            "                                                	when 1 then (select publish from tg_design_doc p where p.`design_id`=mid.`doc_id` and p.del_flg=0) "+
            "                                                	when 2 then (select publish from tg_design_doc p where p.`design_id`=mid.`doc_id` and p.del_flg=0) "+
            "                                                	when 3 then (select publish from itb_template p where p.`tmpl_id`=mid.`doc_id` and p.del_flg=0) "+
            "                                                	when 4 then (select publish from tg_design_doc p where p.`design_id`=mid.`doc_id` and p.del_flg=0) "+
            "                                                	end  "+
            "                                               )as publish , "+
            "                                     (   case mid.type_id "+
            "                                                	when 1 then (select design_name from tg_design_doc p where p.`design_id`=mid.`doc_id` and p.del_flg=0) "+
            "                                                	when 2 then (select design_name from tg_design_doc p where p.`design_id`=mid.`doc_id` and p.del_flg=0) "+
            "                                                	when 3 then (select tmpl_name from itb_template p where p.`tmpl_id`=mid.`doc_id` and p.del_flg=0) "+
            "                                                	when 4 then (select item_name from itb_items p where p.`design_id`=mid.`doc_id` and p.del_flg=0) "+
            "                                                	end  "+
            "                                               )as name , "+
            "                                     (   case mid.type_id "+
            "                                                	when 1 then (select thumb from tg_design_page p where p.`design_id`=mid.`doc_id` and p.page_index=0 and p.del_flg=0) "+
            "                                                	when 2 then (select thumb from tg_design_page p where p.`design_id`=mid.`doc_id` and p.page_index=0 and p.del_flg=0) "+
            "                                                	when 3 then (select thumb_s from itb_tmpl_page p where p.`tmpl_id`=mid.`doc_id` and p.page_index=0 and p.del_flg=0) "+
            "                                                	when 4 then (select func_parse_string_array(thumbs,0)  from itb_items p where p.`design_id`=mid.`doc_id` and p.del_flg=0) "+
            "                                                	end  "+
            "                                               )as thumb , "+
            "                                     (   case mid.type_id "+
            "                                                	when 1 then (select user_id from tg_design_doc d where d.`design_id`=mid.`doc_id` and d.del_flg=0) "+
            "                                                	when 2 then (select user_id from tg_design_doc d where d.`design_id`=mid.`doc_id` and d.del_flg=0) "+
            "                                                	when 3 then (select user_id from itb_template d where d.`tmpl_id`=mid.`doc_id` and d.del_flg=0) "+
            "                                                	when 4 then (select user_id from tg_design_doc d where d.`design_id`=mid.`doc_id` and d.del_flg=0) "+
            "                                                	end  "+
            "                                               ) as user_id , "+
            "                                     (   case mid.type_id "+
            "                                                	when 1 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from tg_design_doc d where d.`design_id`=mid.`doc_id` and d.del_flg=0) "+
            "                                                	when 2 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from tg_design_doc d where d.`design_id`=mid.`doc_id` and d.del_flg=0) "+
            "                                                	when 3 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from itb_template d where d.`tmpl_id`=mid.`doc_id` and d.del_flg=0) "+
            "                                                	when 4 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from tg_design_doc d where d.`design_id`=mid.`doc_id` and d.del_flg=0) "+
            "                                                	end  "+
            "                                     ) as time "+// Added 2017-02-18
            "                             from (	 select r.doc_id, r.type_id "+
            "                                     from tg_keyword_r r "+
            "                                     where r.key_id in( "+
            "                                         select k.key_id "+
            "                                         from tg_keyword k "+
            "                                         where k.del_flg=0 "+
            "                                         "+locate_sql+
            "                                     ) "+
            "                                     and r.del_flg=0 "+
            "             		 				  "+type_sql+
            "                             ) mid "+
            "                     ) mid2  "+
            "                     where mid2.publish=1  "+
            "                     and mid2.id is not null  "+
            "                     limit  "+pageNum*pageSize+","+pageSize+"  ) mid3, itb_user u "+
            "             where mid3.user_id= u.user_id";*/
// todo 修改接口, 已完成, 待审核 begin
        var sql = sql_search(false, keyword, typeLs, pageNum, pageSize);
// todo 修改接口, 已完成, 待审核  end
        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(error){
                callback(error, null);
            }else{
                callback(error, result);
            }
        });
    });
}
// todo 新增接口, 已完成, 待审核 begin
TgKeywordModel.prototype.searchCnt = function(keyword, typeLs, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        var sql = sql_search(true, keyword, typeLs);

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result[0].count);
            }else{
                callback(error, 0);
            }
        });
    });
}
// todo 新增接口, 已完成, 待审核 end


//------------------------------------------------v5.1 搜索相关, bengin------------------------------------------------------------------------
/* 参数 bForCnt: true [仅用于取count]; false [取数据]
* 参数 keyword: 关键字, 多个单词以半角都好分隔
* 参数 typeLs:  搜索分类, 0 未分类, 1 海报, 2 多页, 3 ppt, 4 印品 [多个时,逗号连接: "1,2,3"]
* 参数：callback: 回调, 参数如下:
*          error: 错误消息
*          data: 结果数组, 数组元素如下:
*              "thumb":"http://xxx.jpg", // 如typeL为4则id为印品商品图片,否则为模版图片
*              "id":788, // 如typeL为4则id为印品商品id,否则为模版id
*              "typeL":1,
*              "type_desc":"幻灯片",
*              "price": "免费",
*              "author":"by乌头马角工作室",
*              "time":"2016-12-05 13:38"// 暂定[更新时间]
*/
function sql_search_for_v5_1(bForCnt, keyword, typeLs, pageNum, pageSize){
    var type_sql = typeLs?(" and r.type_id IN ("+typeLs+") "):(" and 1=1 ");

    var keys = keyword.split(',');
    var locate_sql = " and (";      // sample: " and (locate('奇怪', k.`key_name`)>0  or locate('严肃', k.`key_name`)>0 ) ";
    for(var i=0;i<keys.length;i++){
        if(i>0){
            locate_sql +=" or ";
        }
        locate_sql += " locate('"+keys[i]+"', k.`key_name`)>0 ";
    }
    locate_sql +=") ";

    var sql = "";
    if(bForCnt) {
        sql = " SELECT COUNT(*) AS count ";
    } else {
        sql = " SELECT mid3.typeL, mid3.id "+
            "        	           ,( case mid3.typeL "+
            "                             when 1 then '海报' "+
            "                             when 2 then '多页' "+
            "                             when 3 then '幻灯片' "+
            "                             when 4 then (select t.type_name from tg_design_doc p, tg_type t "+
            "                                           where p.item_id=mid3.id and p.del_flg=0 and p.item_id>0 and t.type_id=p.type_id and t.del_flg=0) "+
            "                             end ) AS type_desc "+
            "        	           ,( case mid3.typeL "+
            "                             when 1 then (select design_name from tg_design_doc p where p.`design_id`=mid3.id and p.del_flg=0) "+
            "                             when 2 then (select design_name from tg_design_doc p where p.`design_id`=mid3.id and p.del_flg=0) "+
            "                             when 3 then (select tmpl_name from itb_template p where p.`tmpl_id`=mid3.id and p.del_flg=0) "+
            "                             when 4 then (select item_name from itb_items p where p.item_id=mid3.id and p.state=20 and p.del_flg=0) "+
            "                             end ) AS name "+
            "        	           ,( case mid3.typeL "+
            "                             when 1 then (select thumb from tg_design_page p where p.`design_id`=mid3.id and p.page_index=0 and p.del_flg=0) "+
            "                             when 2 then (select thumb from tg_design_page p where p.`design_id`=mid3.id and p.page_index=0 and p.del_flg=0) "+
            "                             when 3 then (select thumb_s from itb_tmpl_page p where p.`tmpl_id`=mid3.id and p.page_index=0 and p.del_flg=0) "+
            "                             when 4 then (select func_parse_string_array(thumbs,0)  from itb_items p where p.item_id=mid3.id and p.state=20 and p.del_flg=0) "+
            "                             end ) AS thumb "+
            "        	           ,( case mid3.typeL "+
            "                             when 1 then (select u.nickname from tg_design_doc d, itb_user u where d.`design_id`=mid3.id and d.del_flg=0 and u.user_id=d.user_id) "+
            "                             when 2 then (select u.nickname from tg_design_doc d, itb_user u where d.`design_id`=mid3.id and d.del_flg=0 and u.user_id=d.user_id) "+
            "                             when 3 then (select u.nickname from itb_template d, itb_user u where d.`tmpl_id`=mid3.id and d.del_flg=0 and u.user_id=d.user_id) "+
            "                             when 4 then (select u.nickname from tg_design_doc d, itb_user u where d.item_id=mid3.id and d.del_flg=0 and d.item_id>0 and u.user_id=d.user_id) "+
            "                             end ) AS author "+
            "        	           ,( case mid3.typeL "+
            "                             when 1 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from tg_design_doc d where d.`design_id`=mid3.id and d.del_flg=0) "+
            "                             when 2 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from tg_design_doc d where d.`design_id`=mid3.id and d.del_flg=0) "+
            "                             when 3 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from itb_template d where d.`tmpl_id`=mid3.id and d.del_flg=0) "+
            "                             when 4 then (select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i') from tg_design_doc d where d.item_id=mid3.id and d.del_flg=0 and d.item_id>0) "+
            "                             end ) AS time "+
            "        	           ,IFNULL( case mid3.typeL "+
            "                             when 1 then (select concat(TRUNCATE(p.price/100, 2),'元') from itb_tmpl_price p where p.tmpl_id=mid3.id and p.type=1 and p.del_flg=0 order by p.price limit 1) "+
            "                             when 2 then (select concat(TRUNCATE(p.price/100, 2),'元') from itb_tmpl_price p where p.tmpl_id=mid3.id and p.type=2 and p.del_flg=0 order by p.price limit 1) "+
            "                             when 3 then (select concat(TRUNCATE(p.price/100, 2),'元') from itb_tmpl_price p where p.tmpl_id=mid3.id and p.type=3 and p.del_flg=0 order by p.price limit 1) "+
            "                             when 4 then (select concat('印刷 ',TRUNCATE(qp.price/100, 2),'元/',qp.show,qp.unit) "+
            "                                            from itb_items t, itb_print_rel pr, itb_print_param pp, itb_qty_price qp "+
            "                                           where t.item_id=mid3.id and t.del_flg=0 and t.state=20 "+
            "                                             and pr.del_flg=0 AND pr.item_id=t.item_id "+
            "                                             and pp.del_flg=0 AND pp.pp_id=pr.pp_id "+
            "                                             and qp.del_flg=0 AND qp.pp_id=pp.pp_id "+
            "                                           order by qp.min limit 1) "+
            "                             end, '免费' ) AS price ";// 价格信息
    }
    sql +=
        " FROM ( select distinct mid2.* "+
        "          from ( select mid.type_id AS typeL, "+
        "        	           ( case mid.type_id "+
        "                             when 1 then mid.doc_id "+
        "                             when 2 then mid.doc_id "+
        "                             when 3 then mid.doc_id "+
        "                             when 4 then (select item_id from itb_items p where p.`design_id`=mid.`doc_id` and p.state=20 and p.del_flg=0) "+
        "                             end ) AS id "+// 印品时取商品id
        "        	           ,( case mid.type_id "+
        "                             when 1 then (select publish from tg_design_doc p where p.`design_id`=mid.`doc_id` and p.del_flg=0 " +
        "                                           and p.vip=0 ) "+ // add 2017-07-21 增加 vip 过滤
        "                             when 2 then (select publish from tg_design_doc p where p.`design_id`=mid.`doc_id` and p.del_flg=0" +
        "                                           and p.vip=0 ) "+ // add 2017-07-21 增加 vip 过滤
        "                             when 3 then (select publish from itb_template p where p.`tmpl_id`=mid.`doc_id` and p.del_flg=0" +
        "                                           and p.vip=0 ) "+ // add 2017-07-21 增加 vip 过滤
        "                             when 4 then (select publish from tg_design_doc p, tg_type t WHERE p.`design_id`=mid.`doc_id` AND p.del_flg=0 AND p.item_id>0 AND t.type_id = p.type_id AND t.parent in (40100, 40200)" +// 限制印品搜索, 仅能搜索名片传单
        "                                           and p.vip=0 ) "+ // add 2017-07-21 增加 vip 过滤
        "                             end ) AS publish "+
        "                      from ( select r.doc_id, r.type_id "+
        "                               from tg_keyword_r r "+
        "                              where r.key_id in ( SELECT k.key_id FROM tg_keyword k WHERE k.del_flg=0 "+locate_sql+" ) "+
        "                                and r.del_flg=0 "+
        "                                  "+type_sql+
        "                           ) mid "+
        "               ) mid2 "+
        "         where mid2.publish=1 "+
        "           and mid2.id is not null ";
    if(!bForCnt) {// 取数据时
        var skipCnt = Number(pageNum||'0')*Number(pageSize||'0');
        sql += " limit "+skipCnt+", "+Number(pageSize||'0');
    }
    sql += " ) mid3 ";

    return sql;
}
// v5.1 搜索接口, 印品搜索分类下仅搜索名片传单
TgKeywordModel.prototype.search_v5_1 = function(keyword, typeLs, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if(err) {
            console.log("TgKeywordModel.js [search_v5_1] getConnection err: "+ err);
            return callback(err, null);
        }
        var sql = sql_search_for_v5_1(false, keyword, typeLs, pageNum, pageSize);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(error){
                callback(error, null);
            }else{
                callback(error, result);
            }
        });
    });
}
// v5.1 搜索接口, 印品搜索分类下仅搜索名片传单
TgKeywordModel.prototype.searchCnt_v5_1 = function(keyword, typeLs, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if(err) {
            console.log("TgKeywordModel.js [searchCnt_v5_1] getConnection err: "+ err);
            return callback(err, null);
        }
        var sql = sql_search_for_v5_1(true, keyword, typeLs);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();
            if(result && result.length>0){
                callback(error, result[0].count);
            }else{
                callback(error, 0);
            }
        });
    });
}

/**
 *
 # 搜索模版／印品商品
 # 参数：keywords， 关键词(去特殊字符, 以 半角逗号拼接)，字符串类型，形如：红色,名片
 # 参数：typeLs，一级分类的集合，字符串类型，形如：[1,3]
 # 参数：typeMs，二级级分类的集合，字符串类型，形如：[10010_10020,0]
 #		注意：1、数组个数要与typeLs相同
 #			 2、数组的每个元素，是对应一级分类下的二级分类的集合，以半角下划线分割
 #			 3、不限制二级分类时，传入0
 # 参数：pageNum，页码，整数类型，>=0，形如：0
 # 参数：pageSize，页容量，正整数类型，>0，形如：10
 # 参数：withTotalPage，1返回总页数，整数类型，形如：0
 # 参数：includeVipTemplate，1搜索vip模版，0表示不搜索
 #
 # 返回：模版／印品商品列表
 # 注意：    nodejs 调用储存过程后，取得参数中的返回值的方法：console.log(retsult[0][0].f_result);
 #          调用方法：call search_items('黄,名片','[4]','[40100_40200]',0, 40,1,@f_result);
 #          调用方法：call search_items('白','[1,3,4]','[0,0,0]',0, 40,1,@f_result);
 * */

 TgKeywordModel.prototype.search_v5_3 = function (keywords, typeLs, typeMs, pageNum, pageSize, withTotalPage, includeVipTemplate, callback){

     pool.pool.getConnection(function(err, connection) {
         if(err) {
             console.log("TgKeywordModel.js [searchCnt_v5_3] getConnection err: "+ err);
             return callback(err, null);
         }
         var sql = "call search_items2( '" + keywords + "' , '" + typeLs + "' , '" + typeMs + "' , " + pageNum + "," +pageSize + "," + withTotalPage+ "," + includeVipTemplate + ", @f_result" + ")";
         console.log(sql);
         connection.query(sql, function(error, result) {
             // And done with the connection.
             connection.release();
             //console.log();
             if(error) {
                 return callback(error, null);
             }

             return callback(null, result[0] || []);
         });
     });
 };

/**
 * 获取模板关键词列表
 * 参数：designId  设计模板id
 * 返回：模板的关键词信息
 * */

TgKeywordModel.prototype.getDesignKwd = function ( designId, callback){

    pool.pool.getConnection(function(err, connection) {
        if(err) {
            console.log("TgKeywordModel.js [getDesignKwd] getConnection err: "+ err);
            return callback(err, null);
        }
        var sql ="	select	" +
                "	  tk.key_id,	" +
                "	  tk.key_name,	" +
                "	  tk.type	" +
                "	from	" +
                "	  itbour.tg_design_doc tdd,	" +
                "	  itbour.tg_keyword_r tkr,	" +
                "	  itbour.tg_keyword tk	" +
                "	where tdd.design_id = "+designId+"	" +
                "	and tdd.design_id = tkr.doc_id	" +
                "	and tkr.key_id = tk.key_id	" +
                "	and tdd.del_flg = 0	" +
                "	and tkr.del_flg = 0	" +
                "	and tk.del_flg = 0	" ;
            console.log(sql);
        connection.query(sql, function(error, result) {
            connection.release();
            if(error) {
                return callback(error, null);
            }
            return callback(null, result);
        });
    });
};


/**
 * 插入用户自定义关键词
 * 参数：userId  用户的唯一标识
 * 参数: cusKeys  用户自定义关键词数组
 * 返回：模板的关键词信息
 * */

TgKeywordModel.prototype.insertNewDesignCusKeyword = function (userId,cusKeys, callback){

    pool.pool.getConnection(function(err, connection) {
        if(err) {
            console.log("TgKeywordModel.js [insertNewDesignCusKeyword] getConnection err: "+ err);
            return callback(err, null);
        }
        var sql ="	INSERT INTO itbour.tg_keyword	" +
            "	(key_name,`type`,type_name,`order`,create_time,update_time,update_by,del_flg)	" +
            "	VALUES	" ;
        for(var i=0;i<cusKeys.length;i++){
            if(i < cusKeys.length-1){
                sql +="('"+cusKeys[i]+"',10,'自定义',999,SYSDATE(),SYSDATE(),"+userId+",0),";
            }
            if(i == cusKeys.length-1){
                sql +="('"+cusKeys[i]+"',10,'自定义',999,SYSDATE(),SYSDATE(),"+userId+",0)";
            }
        }
        console.log(sql);
        connection.query(sql, function(error, result) {
            connection.release();
            if(error) {
                return callback(error, null);
            }
            return callback(null, result);
        });
    });
};

/**
 * 获取用户自定义关键词id
 * 参数：cusKeys  参数: cusKeys  用户自定义关键词
 * 返回：模板的关键词信息
 * */

TgKeywordModel.prototype.getNewDesignKwd = function (cusKeys, callback){

    pool.pool.getConnection(function(err, connection) {
        if(err) {
            console.log("TgKeywordModel.js [getNewDesignKwd] getConnection err: "+ err);
            return callback(err, null);
        }
        var sql ="select tk.key_id, tk.key_name from itbour.tg_keyword tk where tk.del_flg = 0 and (";
        for(var i=0;i<cusKeys.length;i++){
            if(i==0){sql+=" tk.key_name ='"+cusKeys[i]+"' ";}
            if(i!=0){sql+=" or tk.key_name ='"+cusKeys[i]+"'";}
        }
        sql += ")";
        console.log(sql);
        connection.query(sql, function(error, result) {
            connection.release();
            if(error) {
                return callback(error, null);
            }
            return callback(null, result);
        });
    });
};

/**
 * 获取模板的typeid
 * 参数：designId  设计模板id
 * 返回：模板的关键词信息
 * */

TgKeywordModel.prototype.getDesignTopType = function (designId, callback){

    pool.pool.getConnection(function(err, connection) {
        if(err) {
            console.log("TgKeywordModel.js [getDesignTopType] getConnection err: "+ err);
            return callback(err, null);
        }
        var sql = "	SELECT	" +
                "	  tt.top_parent, " +
                "     tdd.state AS pbl,"+
                "     tdd.state2 AS unpbl,"+
                "     tdd.publish,"+
                "     tdd.user_id  " +
                "	FROM	" +
                "	  itbour.tg_design_doc tdd,	" +
                "	  itbour.tg_type tt	" +
                "	WHERE tdd.design_id = "+designId+"	" +
                "	  AND tdd.type_id = tt.type_id	" +
                "	  AND tdd.del_flg = 0	" +
                "	  AND tt.del_flg = 0;	" ;
            console.log(sql);
        connection.query(sql, function(error, result) {
            connection.release();
            if(error) {
                return callback(error, null);
            }
            return callback(null, result);
        });
    });
};



//------------------------------------------------v5.1 搜索相关, end------------------------------------------------------------------------
//--------------------------------end-------------------------------


// 导出
module.exports = TgKeywordModel;